﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using Microsoft.Practices.Unity;
using WebShop.Contracts;
using WebShop.DAL.Interfaces;
using Webdiyer.WebControls.Mvc;

namespace WebShop.DAL.Repositories
{
    public class ProductRepository : IProductRepository
    {
        [Dependency]
        public NWData Db { get; set; }

        public void Create(Product objectToCreate)
        {
            Db.DataContext.Products.Add(objectToCreate);
        }

        public void Delete(int id)
        {
            Db.DataContext.Entry(Get(id)).State = EntityState.Deleted;
        }

        public void Edit(Product objectToUpdate)
        {
            Db.DataContext.Entry(objectToUpdate).State = EntityState.Modified;
        }

        public Product Get(int id)
        {
            return Db.DataContext.Products.Find(id);
        }

        public Product Find(System.Linq.Expressions.Expression<System.Func<Product, bool>> predicate)
        {
            return Db.DataContext.Products.SingleOrDefault(predicate);
        }

        public System.Collections.Generic.IEnumerable<Product> List()
        {
            return Db.DataContext.Products.ToList();
        }

        public PagedList<Product> List(int? page, int pageSize)
        {
            return Db.DataContext.Products.ToPagedList(page.GetValueOrDefault(1), pageSize);
        }

        public PagedList<Product> FindList(int? page, int pageSize, System.Linq.Expressions.Expression<System.Func<Product, bool>> predicate, System.Linq.Expressions.Expression<System.Func<Product, string>> orderBy, bool ascending)
        {
            var query = Db.DataContext.Products.Include(p => p.Category).Include(p => p.Supplier);

            if (predicate != null)
            {
                query = query.Where(predicate);
            }

            if (orderBy != null)
            {
                if (ascending)
                {
                    query = query.OrderBy(orderBy);
                }
                else
                {
                    query = query.OrderByDescending(orderBy);
                }
            }
            else
            {
                query = query.OrderBy(o => o.ProductName);
            }

            return query.ToPagedList(page.GetValueOrDefault(1), pageSize);
        }

        public IEnumerable<Product> QuickSearch(string qurey, int results)
        {
            string sqlQuery = string.Format("EXEC	[dbo].[QuickSearch] @results = {0}, @query = N'{1}'", results, qurey);

            var data = Db.DataContext.Database.SqlQuery<Product>(sqlQuery).ToList();

            return data;
        }

        public void Detach(Product objectToDetach)
        {
            Db.DataContext.Entry(objectToDetach).State = EntityState.Detached;
        }

        public void SaveChanges()
        {
            Db.DataContext.SaveChanges();
        }


        public PagedList<ProductReport> Report(System.DateTime? startDate, System.DateTime? endDate, int? page, int? pageSize)
        {
            if (!startDate.HasValue)
            {
                startDate = DateTime.MinValue;
            }
            if (!endDate.HasValue)
            {
                endDate = DateTime.Now;
            }

            PagedList<ProductReport> result = (from p in Db.DataContext.Products.AsQueryable()
                                               join odet in Db.DataContext.Order_Details on p.ProductID equals odet.ProductID
                                               join order in Db.DataContext.Orders on odet.OrderID equals order.OrderID
                                               let quantity = p.Order_Details.Sum(od => od.Quantity)
                                               let sum = p.Order_Details.Sum(od => od.Quantity * od.UnitPrice)
                                               let date = p.Order_Details.Select(o => o.Order.OrderDate)
                                               where (order.OrderDate <= endDate && order.OrderDate >= startDate)
                                               //let sum = p.Order_Details.Sum(od => (double)od.Quantity * (double)od.UnitPrice * (1 - od.Discount))
                                               group p by new { p.ProductName, p.ProductID, p.CategoryID, p.Category.CategoryName, count = quantity, sum } into grp
                                               orderby grp.Key.count descending
                                               select new ProductReport
                                               {
                                                   ProductName = grp.Key.ProductName,
                                                   ProductID = grp.Key.ProductID,
                                                   CategoryName = grp.Key.CategoryName,
                                                   CategoryID = grp.Key.CategoryID.Value,
                                                   Amount = grp.Key.count,
                                                   Sum = grp.Key.sum
                                               }).ToPagedList(page.GetValueOrDefault(1), pageSize.GetValueOrDefault(10));

            /*PagedList<ProductReport> query = (from pr in db.Products
                                              join cat in db.Categories on pr.CategoryID equals cat.CategoryID
                                              join od in db.Order_Details on pr.ProductID equals od.ProductID
                                              join ord in db.Orders on od.OrderID equals ord.OrderID
                                              where ord.OrderDate >= StartDate && ord.OrderDate <= EndDate
                                              group od by new { pr.ProductName, cat.CategoryName } into sel
                                              select (new ProductReport
                                              {
                                                  ProductName = sel.Key.ProductName,
                                                  CategoryName = sel.Key.CategoryName,
                                                  Amount = sel.Sum(a => a.Quantity),
                                                  Sum = sel.Sum(a => (a.Quantity * a.UnitPrice))
                                              })).OrderByDescending(p => p.Amount).ToPagedList(pageIndex, PAGE_SIZE);*/
            return result;
        }
    }
}